﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.Common;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using WindowsFormsApplication1.Common;
using FanjiaHotelPlatform.utils.Kits;
using Maticsoft.DBUtility;
using MySql.Data.MySqlClient;
using Z.BulkOperations;

namespace WindowsFormsApplication1
{
    public partial class BrandMergeOpt : Form
    {
        public BrandMergeOpt()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            var brandName = "汉庭";

            var dtCityMapping = DbHelperMySQL.Query("SELECT * FROM huazhucitymapping ORDER BY HuaZhuCityNo", "MysqlConn").Tables[0];

            var dtHotelCheckSim = DbHelperMySQL
                .Query($"SELECT * FROM hotelchecksim WHERE 1=0", "MysqlConn").Tables[0];

            var dtBrand = DbHelperMySQL
                .Query("SELECT DISTINCT BrandName FROM `huazhuhotelinfo`", "MysqlConn").Tables[0];

            DbHelperMySQL
                .Query($"TRUNCATE TABLE hotelchecksim", "MysqlConn");

            if (dtBrand != null && dtBrand.Rows.Count > 0)
            {
                foreach (DataRow drBrand in dtBrand.Rows)
                {
                    brandName = drBrand["BrandName"].ToString();
                    MapByBrand(dtCityMapping, brandName, dtHotelCheckSim);

                    dtHotelCheckSim.Clear();
                }
            }

            MessageBox.Show("OK");
        }

        private static void MapByBrand(DataTable dtCityMapping, string brandName, DataTable dtHotelCheckSim)
        {
            if (dtCityMapping != null && dtCityMapping.Rows.Count > 0)
            {
                foreach (DataRow dr in dtCityMapping.Rows)
                {
                    var cityName = dr["FanjiaCityName"].ToString();

                    //if (cityName != "杭州")
                    //{
                    //    continue;
                    //}

                    //查询华住该城市下该品牌的所有酒店
                    var dtHotelSp = DbHelperMySQL
                        .Query(
                            $"SELECT * FROM huazhuhotelinfo WHERE CityNo='{dr["HuaZhuCityNo"].ToString()}' AND BrandName='{brandName}'",
                            "MysqlConn").Tables[0];

                    //查询泛嘉库包含该品牌名称的酒店
                    var dtHotelFanjia = DbHelperSQL
                        .Query(
                            $"SELECT Id,[Name],[Address],[Phone],[Longitude],[Latitude] FROM [HotelBasicInfoes] WHERE CityId='{dr["FanjiaCityId"].ToString()}' AND Name LIKE '%{brandName}%' AND [IsDeleted]=0")
                        .Tables[0];

                    var dtHotelMapping = DbHelperSQL
                        .Query(
                            "SELECT * FROM [HotelMappings] where supplierid='89E38E71-47D3-4946-92F4-B41E55ECD38F' AND IsDeleted=0 AND IsActive=1")
                        .Tables[0];

                    if (dtHotelSp != null && dtHotelSp.Rows.Count > 0)
                    {
                        foreach (DataRow drHsp in dtHotelSp.Rows)
                        {
                            var hotelName = drHsp["HotelName"].ToString();
                            var hotelPhone = drHsp["Telephone"].ToString();
                            var supplierHotelId = drHsp["HotelNo"].ToString();
                            var spAddress = drHsp["HotelAddress"].ToString();

                            var lat = ConvertKit.ToDecimal(drHsp["FanjiaLatitude"]);
                            var lng = ConvertKit.ToDecimal(drHsp["FanjiaLongitude"]);

                            var keyWord = hotelName.Replace(brandName, "").Replace("酒店", "");

                            var dtLike = dtHotelFanjia.Select($"Name LIKE '%{keyWord}%'");

                            if (dtLike != null && dtLike.Length > 0)
                            {
                                foreach (DataRow drLik in dtLike)
                                {
                                    var likeHotelName = drLik["Name"].ToString();
                                    var likePhone = drLik["Phone"].ToString();
                                    var hotelId = drLik["Id"].ToString();
                                    var address = drLik["Address"].ToString();

                                    var htMapping =
                                        dtHotelMapping.Select($"HotelId='{hotelId}' AND SupplierHotelId='{supplierHotelId}'");

                                    if (htMapping.Length > 0)
                                        continue;

                                    var hotelNameSimilarity =
                                        SimilarityGenerator.GetNameSimilarity(hotelName, likeHotelName);
                                    var hotelPhoneSimilarity =
                                        SimilarityGenerator.GetPhoneSimilarity(hotelPhone, likePhone);

                                    var latSupplier = ConvertKit.ToDecimal(drLik["Latitude"]);
                                    var lngSupplier = ConvertKit.ToDecimal(drLik["Longitude"]);

                                    var hotelCoordinateSimilarity = 0D;
                                    if (lat > 0 && lng > 0 && latSupplier > 0 && lngSupplier > 0)
                                    {
                                        hotelCoordinateSimilarity =
                                            SimilarityGenerator.GetCoordinateSimilarity(lat, lng, latSupplier, lngSupplier);
                                    }

                                    DataRow drNew = dtHotelCheckSim.NewRow();
                                    drNew["HotelNameSp"] = hotelName;
                                    drNew["HotelNameFj"] = likeHotelName;
                                    drNew["PhoneSp"] = hotelPhone;
                                    drNew["PhoneFj"] = likePhone;
                                    drNew["AddressSp"] = spAddress;
                                    drNew["AddressFj"] = address;
                                    drNew["HotelId"] = hotelId;
                                    drNew["SupplierHotelId"] = supplierHotelId;
                                    drNew["NameSimilarity"] = hotelNameSimilarity;
                                    drNew["PhoneSimilarity"] = hotelPhoneSimilarity;
                                    drNew["CoordinateSimilarity"] = hotelCoordinateSimilarity;
                                    drNew["CreationTime"] = DateTime.Now;

                                    dtHotelCheckSim.Rows.Add(drNew);
                                }
                            }
                        }
                    }
                }
            }

            if (dtHotelCheckSim.Rows.Count > 0)
            {
                

                var mysqlConnStr = DbHelperMySQL.GetConnStr("MysqlConn");

                try
                {
                    using (DbConnection connection = new MySqlConnection(mysqlConnStr))
                    {
                        dtHotelCheckSim.TableName = "hotelchecksim";
                        connection.Open();
                        var bulk = new BulkOperation(connection);
                        bulk.AutoMapKeyName = "HotelId";
                        bulk.BulkMerge(dtHotelCheckSim);
                        //bulk.BulkUpdate(dt);
                        //bulk.BulkDelete(dt);
                        //bulk.BulkMerge(dt);
                        //bulk.BulkSaveChanges(ds);
                        //bulk.BulkSynchronize(dt);
                    }
                }
                catch (Exception xe)
                {
                    MessageBox.Show(xe.Message);
                }
            }
        }

        /*
        await Task.Run(() =>
            {
            var supplier = GetHotelAppServiceBySupplierId(checkDto.SupplierId);
            checkDto.SupplierName = supplier?.Supplier.Title;

            checkDto.OrderSupplierId = Supplier.Id;
            checkDto.OrderSupplierName = Supplier.Title;

            checkDto.IsNotSame = true;

            var hotelNameSimilarity =
                SimilarityGenerator.GetNameSimilarity(checkDto.HotelName, checkDto.HotelNameSupplier);
            checkDto.HotelNameSimilarity = ConvertKit.ToDecimal(hotelNameSimilarity);
            checkDto.HotelPhoneSimilarity =
                SimilarityGenerator.GetPhoneSimilarity(checkDto.TelPhone, checkDto.TelPhoneSupplier);

            var lat = ConvertKit.ToDecimal(checkDto.Lat);
            var lng = ConvertKit.ToDecimal(checkDto.Lng);
            var latSupplier = ConvertKit.ToDecimal(checkDto.LatSupplier);
            var lngSupplier = ConvertKit.ToDecimal(checkDto.LngSupplier);

            var hotelCoordinateSimilarity = 0D;
            if (lat > 0 && lng > 0 && latSupplier > 0 && lngSupplier > 0)
            {
                hotelCoordinateSimilarity =
                    SimilarityGenerator.GetCoordinateSimilarity(lat, lng, latSupplier, lngSupplier);
                checkDto.HotelCoordinateSimilarity = ConvertKit.ToDecimal(hotelCoordinateSimilarity);
            }

            //先比较是否同一供应商的酒店资料
            if (checkDto.SupplierId == checkDto.OrderSupplierId)
            {
                //同一供应商名称一致，则通过
                if (checkDto.HotelName == checkDto.HotelNameSupplier)
                {
                    checkDto.IsNotSame = false;
                }
                //同一供应商名称不一致（名称相似度在90%以上）、电话一致、经纬度在200米范围内，则通过
                else if ((hotelNameSimilarity > 0.9D && checkDto.HotelPhoneSimilarity == 1) ||
                         (hotelNameSimilarity > 0.82D && checkDto.HotelPhoneSimilarity == 1 && hotelCoordinateSimilarity >= 0.9D))
                {
                    checkDto.IsNotSame = false;
                }
            }
            else
            {
                if (checkDto.HotelName == checkDto.HotelNameSupplier)
                {
                    if (hotelCoordinateSimilarity >= 0.8D)
                        checkDto.IsNotSame = false;
                }

                //不同供应商的酒店，名称相似度在95%以上、电话相同、经纬度在200M范围内
                if ((hotelNameSimilarity >= 0.9D && checkDto.HotelPhoneSimilarity == 1) ||
                    (hotelNameSimilarity > 0.78D && checkDto.HotelPhoneSimilarity == 1 && hotelCoordinateSimilarity >= 0.9D))
                {
                    checkDto.IsNotSame = false;
                }
            }

            //判断酒店名称中，是否包含集团/连锁酒店关键词，且是否存在不一致的情况
            if (checkDto.IsNotSame == false) //如果校验通过，则继续校验品牌关键字
            {
                var keyList = GetBrandKeyList();
                if (keyList == null || keyList.Count == 0)
                    return;

                var hotelNameFs = new List<int>();
                var hotelNameSupplierFs = new List<int>();

                for (int i = 0; i < keyList.Count; i++) //轮询品牌关键字集合，判断酒店名称中是否包含关键字
                {
                    if (checkDto.HotelName.Contains(keyList[i]))
                    {
                        hotelNameFs.Add(i);
                    }
                    if (checkDto.HotelNameSupplier.Contains(keyList[i]))
                    {
                        hotelNameSupplierFs.Add(i);
                    }
                }

                //如果酒店名称中包含关键字
                if (hotelNameFs.Count > 0 || hotelNameSupplierFs.Count > 0)
                {
                    checkDto.IsNotSame = true;

                    if (hotelNameFs.Count == hotelNameSupplierFs.Count)
                    {
                        //酒店名称和供应商酒店名称所含关键词数量相同
                        int cCount = 0;
                        for (int i = 0; i < hotelNameFs.Count; i++)
                        {
                            if (hotelNameFs[i] == hotelNameSupplierFs[i])
                            {
                                cCount++;
                            }
                            else
                            {
                                break;
                            }
                        }

                        //酒店名称和供应商酒店名称所含关键词都相同，则验证通过
                        if (cCount > 0 && cCount == hotelNameFs.Count)
                        {
                            checkDto.IsNotSame = false;
                        }
                    }

                    if (checkDto.IsNotSame)
                    {
                        checkDto.HotelNameSimilarity = -checkDto.HotelNameSimilarity;
                    }
                }
            }
        });
        
        */

    }
}
